﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Reflection;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using TY.UC.Common;
using System.Web;
using System.Text;

namespace TY.UT.Excel
{
    public class ExcelHelper
    {
        /// <summary>
        /// 导出excel
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="list"></param>
        /// <returns></returns>
        public static byte[] ExportByList(string filePath, List<List<string>> list)
        {
            try
            {
                if (!File.Exists(filePath) || list.Count <= 0) return null;
                FileStream fs = File.OpenRead(filePath);
                //.xls使用HSSFWorkbook
                IWorkbook wk = new XSSFWorkbook(fs);
                fs.Close();

                IFont font = wk.CreateFont();
                font.FontHeightInPoints = 9;
                font.FontName = "宋体";

                ICellStyle cellStyle = wk.CreateCellStyle();
                cellStyle.Alignment = HorizontalAlignment.General;      // 设置单元格水平方向对齐方式
                cellStyle.VerticalAlignment = VerticalAlignment.Center; // 设置单元格垂直方向对齐方式
                cellStyle.SetFont(font);

                ISheet sheet1 = wk.GetSheetAt(0);

                for (int r = 0; r < list.Count; r++)
                {
                    var row = sheet1.CreateRow(r + 1);
                    CreateCell(row, list[r], cellStyle);
                }

                MemoryStream ms = new MemoryStream();
                wk.Write(ms);
                var bytes = ms.ToArray();
                ms.Close();
                ms.Dispose();
                return bytes;
            }
            catch (Exception e)
            {
                LogHelper.WriteLog("ExcelHelper", "ExcelHelper-ExportByList", list, e);
                return null;
            }
        }

        /// <summary>
        /// 导出Excel(注意:类型T里面的属性全部导出，并且导出模版里列的顺序必须和类型T里面建立的属性的顺序一致)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="filePath"></param>
        /// <param name="list"></param>
        /// <returns></returns>
        public static byte[] ExportByList<T>(string filePath, List<T> list)
        {
            try
            {
                if (!File.Exists(filePath) || list.Count <= 0) return null;
                FileStream fs = File.OpenRead(filePath);
                //.xls使用HSSFWorkbook
                IWorkbook wk = new XSSFWorkbook(fs);
                fs.Close();

                IFont font = wk.CreateFont();
                font.FontHeightInPoints = 9;
                font.FontName = "宋体";

                ICellStyle cellStyle = wk.CreateCellStyle();
                cellStyle.Alignment = HorizontalAlignment.General;      // 设置单元格水平方向对齐方式
                cellStyle.VerticalAlignment = VerticalAlignment.Center; // 设置单元格垂直方向对齐方式
                cellStyle.SetFont(font);

                ISheet sheet1 = wk.GetSheetAt(0);

                for (int r = 0; r < list.Count; r++)
                {
                    var item = list[r];
                    List<string> valList = new List<string>();

                    Type t = item.GetType();//获得该类的Type
                    foreach (PropertyInfo pi in t.GetProperties())
                    {
                        object value1 = pi.GetValue(item, null);//用pi.GetValue获得值
                        valList.Add(value1.GetType() != typeof(DateTime)
                            ? value1.ToString()
                            : ((DateTime)value1).ToString("yyyy-MM-dd HH:mm:ss"));
                    }

                    var row = sheet1.CreateRow(r + 1);
                    CreateCell(row, valList, cellStyle);
                }

                MemoryStream ms = new MemoryStream();
                wk.Write(ms);
                var bytes = ms.ToArray();
                ms.Close();
                ms.Dispose();
                return bytes;
            }
            catch (Exception e)
            {
                LogHelper.WriteLog("ExcelHelper", "ExcelHelper-ExportByList<T>:filePath=" + filePath,list, e);
                return null;
            }
        }

        /// <summary>
        /// 创建一个单元格并为其设定指定的对齐方式
        /// yzb
        /// 2015-11-13
        /// </summary>
        /// <param name="row">列</param>
        /// <param name="val">值</param>
        /// <param name="cellStyle">单元格样式</param>
        private static void CreateCell(IRow row, List<string> val, ICellStyle cellStyle)
        {
            for (int i = 0; i < val.Count; i++)
            {
                var v = val[i];
                if (string.IsNullOrEmpty(v)) continue;

                ICell cell = row.CreateCell(i);// 创建单元格
                cell.SetCellValue(val[i]);     // 设置值
                cell.CellStyle = cellStyle;    // 设置单元格样式
            }
        }

        
      
        /// <summary>
        /// 导入excel
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static DataTable ReadFromExcelFile(string filePath)
        {
            try
            {
                FileStream fs = File.OpenRead(filePath);
                IWorkbook wk;
                string extension = Path.GetExtension(filePath);
                if (extension != null && extension.Equals(".xls"))
                {
                    //把xls文件中的数据写入wk中
                    wk = new HSSFWorkbook(fs);
                }
                else
                {
                    //把xlsx文件中的数据写入wk中
                    wk = new XSSFWorkbook(fs);
                }

                fs.Close();
                //读取当前表数据
                ISheet sheet = wk.GetSheetAt(0);
                IRow row = sheet.GetRow(0);  //读取当前行数据

                DataTable dt = new DataTable();

                foreach (ICell cell in row.Cells)
                {
                    dt.Columns.Add(cell.StringCellValue);
                }
                var firstRow = sheet.GetRow(1).GetCell(0) + "";
                if (string.IsNullOrEmpty(firstRow))
                    return dt;

                int cellNum = row.Cells.Count;
                for (int i = 1; i <= sheet.LastRowNum; i++)
                {
                    row = sheet.GetRow(i);  //读取当前行数据
                    if (row == null) continue;
                    DataRow tbRow = dt.NewRow();
                    for (int j = 0; j < cellNum; j++)
                    {
                        string value = row.GetCell(j) + "";
                        if (string.IsNullOrEmpty(value)) continue;
                        tbRow[j] = value;
                    }
                    dt.Rows.Add(tbRow);
                }
                return dt;
            }

            catch (Exception e)
            {
                LogHelper.WriteLog("ExcelHelper", "ExcelHelper-ExportByList<T>:filePath=" + filePath, e);
                return null;
            }
        }

        /// <summary>
        /// 输入HTTP头，然后把指定的流输出到指定的文件名
        /// yzb
        /// 2015-09-03
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="bytes"></param>
        /// <param name="timestamp"></param>
        public static void ExportToExcel(string fileName, byte[] bytes, string timestamp = "")
        {
            HttpCookie ck = new HttpCookie(timestamp, "1") { Expires = DateTime.Now.AddMinutes(2) };
            HttpContext.Current.Response.ContentType = "*/*";
            HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
            HttpContext.Current.Response.Charset = "utf-8";
            if (HttpContext.Current.Request.ServerVariables["http_user_agent"].ToLower().IndexOf("firefox") == -1)
            {
                HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8).ToString());
            }
            else
            {
                HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
            }

            HttpContext.Current.Response.AppendCookie(ck);
            HttpContext.Current.Response.AppendHeader("Content-Length", bytes.Length.ToString());
            HttpContext.Current.Response.BinaryWrite(bytes);
            HttpContext.Current.Response.End();
        }
    }
}
